-- @owner: opentestcase016
-- @date: 2022-10-24
-- @testpoint: string测试,使用函数trim,initcap,substr,length,case when,inner join嵌套子查询,可以下推

--step1:创建表并插入数据 expect:成功	
drop table if exists t_ustore_aggfunc_0022_01 cascade;
create table t_ustore_aggfunc_0022_01 (
    c_id          int,
    c_d_id        int,
    c_w_id        int,
    c_first       varchar(16),
    c_middle      char(2),
    c_last        varchar(16),
    c_street_1    varchar(20),
    c_street_2    varchar(20),
    c_city        varchar(20),
    c_state       char(2),
    c_zip         char(9),
    c_phone       char(16),
    c_since       timestamp,
    c_credit      char(2),
    c_credit_lim  numeric(12,2),
    c_discount    numeric(4,4),
    c_balance     numeric(12,2),
    c_ytd_payment numeric(12,2),
    c_payment_cnt int,
    c_delivery_cnt int,
    c_data varchar(500))
with (storage_type=ustore) partition by range (c_id)
(
partition vector_engine_t_ustore_aggfunc_0022_01_1 values less than (10),
partition vector_engine_t_ustore_aggfunc_0022_01_2 values less than (77),
partition vector_engine_t_ustore_aggfunc_0022_01_3 values less than (337),
partition vector_engine_t_ustore_aggfunc_0022_01_4 values less than (573),
partition vector_engine_t_ustore_aggfunc_0022_01_5 values less than (1357),
partition vector_engine_t_ustore_aggfunc_0022_01_6 values less than (2033),
partition vector_engine_t_ustore_aggfunc_0022_01_7 values less than (2087),
partition vector_engine_t_ustore_aggfunc_0022_01_8 values less than (2387),
partition vector_engine_t_ustore_aggfunc_0022_01_9 values less than (2687),
partition vector_engine_t_ustore_aggfunc_0022_01_10 values less than (2987),
partition vector_engine_t_ustore_aggfunc_0022_01_11 values less than (maxvalue)
);

insert into t_ustore_aggfunc_0022_01 values(1,1,1,'iscmvlstpn','oe','barbarbar','bkilipzfcxcle','pmbwodmpvhvpafbj','dyfaoptppzjcgjrvyqa','uq','480211111','9400872216162535','2013/1/4 11:26:41','gc',50000.00,0.4361,-10.00,10.00,1,0,'qvldetanrbrburbmzqujshoqnggsmnteccipriirdhirwiynpfzcsykxxyscdsfqafhatdokmjogfgslucunvwbtbfsqzjeclbacpjqdhjchvgbnrkjrgjrycsgppsocnevautzfeosviaxbvobffnjuqhlvnwuqhtgjqsbfacwjpbvpgthpyxcpmnutcjxrbxxbmrmwwxcepwiixvvleyajautcesljhrsfsmsnmzjcxvcuxdwmyijbwywiirsgocwktedbbokhynznceaesuifkgoaafagugetfhbcylksrjukvbufqcvbffaxnzssyquidvwefktknrchyxfphunqktwnipnsrvqswsymocnoexbabwnpmnxsvshdsjhazcauvqjgvqjfkjjgqrceyjmbumkapmcbxeashybpgekjkfezthnjbhfqiwbutbxtkjkndyylrvrhsazhijvmkmhdgvuyvyayiavdmypqomgobo');
insert into t_ustore_aggfunc_0022_01 values(2,1,1,'iscmvlstpn','oe','barbarbar','bkilipzfcxcle','pmbwodmpvhvpafbj','dyfaoptppzjcgjrvyqa','uq','480211111','9400872216162535','2013/1/4 11:26:41','gc',50000.00,0.4361,-10.00,10.00,1,0,'qpqomgobo');
insert into t_ustore_aggfunc_0022_01 values(3,1,1,'iscmvlstpn','oe','barbarbar','bkilipzfcxcle','pmbwodmpvhvpafbj','dyfaoptppzjcgjrvyqa','uq','480211111','9400872216162535','2013/1/4 11:26:41','gc',50000.00,0.4361,-10.00,10.00,1,0,'qpqomgobo');
insert into t_ustore_aggfunc_0022_01 values(4,2,1,'iscmvlstpn','oe','barbarbar','bkilipzfcxcle','pmbwodmpvhvpafbj','dyfaoptppzjcgjrvyqa','uq','480211111','9400872216162535','2013/1/4 11:26:41','gc',50000.00,0.4361,-10.00,10.00,1,0,'qpqomgobo');
insert into t_ustore_aggfunc_0022_01 values(5,2,1,'iscmvlstpn','oe','barbarbar','bkilipzfcxcle','pmbwodmpvhvpafbj','dyfaoptppzjcgjrvyqa','uq','480211111','9400872216162535','2013/1/4 11:26:41','gc',50000.00,0.4361,-10.00,10.00,1,0,'qpqomgobo');
insert into t_ustore_aggfunc_0022_01 values(6,3,2,'iscmvlstpn','oe','barbarbar','bkilipzfcxcle','pmbwodmpvhvpafbj','dyfaoptppzjcgjrvyqa','uq','480211111','9400872216162535','2013/1/4 11:26:41','gc',50000.00,0.4361,-10.00,10.00,1,0,'qpqomgobo');
insert into t_ustore_aggfunc_0022_01 values(7,3,2,'iscmvlstpn','oe','barbarbar','bkilipzfcxcle','pmbwodmpvhvpafbj','dyfaoptppzjcgjrvyqa','uq','480211111','9400872216162535','2013/1/4 11:26:41','gc',50000.00,0.4361,-10.00,10.00,1,0,'qpqomgobo');

drop table if exists t_ustore_aggfunc_0022_02;
create table t_ustore_aggfunc_0022_02
(
 d_street_1 varchar(20),
 d_city varchar(20),
 d_zip char(20) default 'djhaksdqd22d',
 d_id integer,
 d_timestamptz timestamp with time zone,
 d_timestamp timestamp without time zone,
 d_interval varchar(20) default '2days 03:30:00',
 d_decimal decimal(10) default 988082,
 d_text text not null,
 d_numeric numeric,
 d_date date default '2015-02-15') with (storage_type=ustore);

create index i_ustore_aggfunc_0022_01 on t_ustore_aggfunc_0022_02(d_street_1,d_interval) ;
create index i_ustore_aggfunc_0022_02 on t_ustore_aggfunc_0022_02(d_text);
create index i_ustore_aggfunc_0022_03 on t_ustore_aggfunc_0022_02(d_date) ;


--step2:创建序列,开启匿名块 expect:成功
drop sequence if exists s_ustore_aggfunc_0022;
create sequence s_ustore_aggfunc_0022 start with 1 maxvalue 500 increment by 1 cycle;
declare
x number;
begin
x := 0;
loop
  insert into t_ustore_aggfunc_0022_02 (d_street_1,d_city,d_zip,d_id,d_timestamptz,d_timestamp,d_text,d_numeric) values ('autyfiueifjol','halaimazi','lanilolia',s_ustore_aggfunc_0022.nextval,'2014-04-24 22:50:31.000000 +8:00','2014-04-30 22:15:31','222utuygtu',1245.33);
  x := x + 1;
  exit when x > 499;
end loop;
end;
/

--step3:string测试,使用函数trim,initcap,substr,length,case when,inner join嵌套子查询,可以下推;expect:成功
select concat_ws('|',trim(both 'x' from t1.c_street_1)),t1.c_city,'',substr(concat_ws('|',t1.c_zip),5),concat_ws(',',t1.c_w_id),'ab'
from t_ustore_aggfunc_0022_01 t1
inner join (select concat_ws(';',d_street_1),d_numeric,d_id,d_text,
 case when length(concat_ws(';',d_city))<>20 then '0'
 else '20'
 end
 from t_ustore_aggfunc_0022_02
 where d_text='sdasdasdasdfff'
 ) s3
on s3.d_numeric=t1.c_d_id
and s3.d_id=100
and s3.d_text like '%a%'
order by 1,2,3,4,5,6 limit 5;

--step4:清理环境 expect:成功
drop sequence if exists s_ustore_aggfunc_0022;
drop table if exists t_ustore_aggfunc_0022_01;
drop table if exists t_ustore_aggfunc_0022_02;
